package com.guorui.dao.impl;

import com.guorui.bean.RealEstate;
import com.guorui.bean.Users;
import com.guorui.dao.UsersDao;
import com.guorui.utils.DruidUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class UsersDaoImpl implements UsersDao {

    private QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());

    @Override
    public Users yanzhen(String cardid, String password) {
        String sql = "select * from users where users.card_id=? and users.password=?";
        try {
            return queryRunner.query(sql,new BeanHandler<Users>(Users.class),cardid,password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public Users rigistfindid(String cardid) {
        String sql = "select * from users where users.card_id=?";
        try {
            return queryRunner.query(sql,new BeanHandler<Users>(Users.class),cardid);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int addusers(Users users) {
        String sql = "insert into users values(?,?,?,?,?,?)";
        try {
            return queryRunner.update(sql,users.getCardId(),users.getName(),users.getSex(),users.getLastModifyTime(),users.getPassword(),users.getStatus());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    //i为用户名和身份证，i1为人为输入的身份证和姓名
    @Override
    public List<RealEstate> findall(String i1,int i,int first,int size) {
        StringBuffer sql = new StringBuffer("select real_estate.id,real_estate.card_id cardId,real_estate.project_name projectName,real_estate.address,real_estate.house_type houseType,real_estate.area,real_estate.build_time buildTime,users.name from users,real_estate where users.card_id = real_estate.card_id");
        if(i==0){
            sql.append(" order by id asc limit ?,?  ");
            try {
                return queryRunner.query(sql.toString(),new BeanListHandler<RealEstate>(RealEstate.class),first,size);
            } catch (SQLException e) {
                e.printStackTrace();
            }
//
        }else if(i==1){
            if(i1==null){
                sql.append(" order by id asc limit ?,?  ");
                try {
                    return queryRunner.query(sql.toString(),new BeanListHandler<RealEstate>(RealEstate.class),first,size);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }else {
                sql.append(" and users.name=? ");
                sql.append(" order by id asc limit ?,?  ");
                try {
                    return queryRunner.query(sql.toString(),new BeanListHandler<RealEstate>(RealEstate.class),i1,first,size);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }else {
            if(i1==null){
                try {
                    sql.append(" order by id asc limit ?,?  ");
                    return queryRunner.query(sql.toString(),new BeanListHandler<RealEstate>(RealEstate.class),first,size);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }else {
                sql.append(" and users.card_id=? ");
                sql.append(" order by id asc limit ?,?  ");
                try {
                    return queryRunner.query(sql.toString(),new BeanListHandler<RealEstate>(RealEstate.class),i1,first,size);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
//        sql.append(" order by id asc limit ?,?  ");




        return null;
    }

    @Override
    public int add(RealEstate realEstate) {
        String sql = "insert into real_estate values(null,?,?,?,?,?,?)";
        try {
            return queryRunner.update(sql,realEstate.getCardId(),realEstate.getProjectName(),realEstate.getAddress(),realEstate.getHouseType(),realEstate.getArea(),realEstate.getBuildTime());
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return 0;
    }

    @Override
    public int delete(int id) {
        String sql = "delete  from real_estate where id = ?";
        try {
            return queryRunner.update(sql,id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public RealEstate findById(int id) {

        String sql = "select real_estate.id,real_estate.card_id cardId,real_estate.project_name projectName,real_estate.address,real_estate.house_type houseType,real_estate.area,real_estate.build_time buildTime,users.name from users,real_estate where users.card_id = real_estate.card_id and real_estate.id=?";
        try {
            return queryRunner.query(sql, new BeanHandler<RealEstate>(RealEstate.class), id);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public int update(RealEstate realEstate) {
        String sql = "update real_estate set card_id=?,project_name = ?,address = ?,house_type = ?,area = ?,build_time = ? where id = ?";
        try {
            return queryRunner.update(sql, realEstate.getCardId(),realEstate.getProjectName(),realEstate.getAddress(),realEstate.getHouseType(),realEstate.getArea(),realEstate.getBuildTime(),realEstate.getId());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    //i为下拉框
    public long total(String i1,Integer i) {
        StringBuffer sql = new StringBuffer("select count(*) from real_estate,users where users.card_id = real_estate.card_id ");
        if (i == 0) {
            try {
                return queryRunner.query(sql.toString(), new ScalarHandler<Long>());
            } catch (SQLException e) {
                e.printStackTrace();
            }
//
        } else if (i == 1) {
            if (i1 == null) {
                try {
                    return queryRunner.query(sql.toString(), new ScalarHandler<Long>());
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            } else {
                sql.append(" and users.name=? ");
                try {
                    return queryRunner.query(sql.toString(), new ScalarHandler<Long>(), i1);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        } else {
            if (i1 == null) {
                try {
                    return queryRunner.query(sql.toString(), new ScalarHandler<Long>());
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            } else {
                sql.append(" and users.card_id=? ");
                try {
                    return queryRunner.query(sql.toString(), new ScalarHandler<Long>(), i1);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
//        sql.append(" order by id asc limit ?,?  ");


        return 0L;


//        if(type1 != 0){
//            sql.append(" and districtId = ? ");
//            try {
//                return queryRunner.query(sql.toString(),new ScalarHandler<Long>(),type1);
//            } catch (SQLException e) {
//                e.printStackTrace();
//            }
//        }
//        try {
//            return queryRunner.query(sql.toString(),new ScalarHandler<Long>());
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//        return 0L;
//    }
    }

    @Override
    public int down(int a) {
        String sql = "";
        return 0;
    }
}
